package com.artfii;

import com.alibaba.fastjson.JSON;
import org.joda.time.DateTime;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.annotation.Resource;
import java.util.Date;
import java.util.List;

@Controller
@RequestMapping("/test/sharding")
public class TestShardingController {

    @Resource
    private JdbcTemplate jdbcTemplate;
    @Resource
    private TestShardingService testShardingService;


    @RequestMapping(value = "list")
    @ResponseBody
    public List list() {
//        String sql = " select * from test_sharding where DATE(create_date) >'2019'";
//        String sql = " select * from test_sharding where  DATE(create_date) <= '2019-12-07'";
//        String sql = " select * from test_sharding where  create_date <='2019-12-08'";
//        String sql = " select * from test_sharding where create_date >'2019-01-01'";
//        String sql = " select * from test_sharding where create_date >='2019-01-01' and create_date <='2020-12-30'";
//        String sql = " select * from test_sharding where create_date >'2019-01-01'";
        String sql = " select * from test_sharding where create_date between '2019-01-01' and '2020-12-30' ";
//        String sql = " select * from test_sharding  ";
        List<TestSharding> shardingList = jdbcTemplate.query(sql, new BeanPropertyRowMapper(TestSharding.class));
        return shardingList;
    }


    @RequestMapping(value = "list/h")
    @ResponseBody
    public String listOfHibernaet() {
        Date start = new DateTime("2019-01-01").toDate();
        Date end = new DateTime("2020-12-30").toDate();
        List<TestSharding> shardingList = testShardingService.findListOfStartDate(start);
//        List<TestSharding> shardingList = testShardingService.findListOfEndDate(end);
//        List<TestSharding> shardingList = testShardingService.findListOfBothDate(start,end);
//        List<TestSharding> shardingList = testShardingService.findListOfBetweenDate(start, end);
        return JSON.toJSONString(shardingList);
    }


    @RequestMapping(value = "add/h")
    @ResponseBody
    public String addOfHibernate() {
        TestSharding ts = new TestSharding();
        ts.setUserName("ponket");
        ts.setCreateDate(new Date());
        ts.setAge(18);
        testShardingService.save(ts);
        return JSON.toJSONString(ts);
    }

    @RequestMapping(value = "add/j")
    @ResponseBody
    public String addOfjdbc() {
        String sql = " insert into test_sharding (id,user_name,age,create_date) values(?,?,?,?)";
        jdbcTemplate.update(sql, IdGen.ONLY.getDefId(), "ponket", 18, DateTime.now().toString("yyyy-MM-dd HH:mm:ss"));
        return sql;
    }


    /**
     * 测试聚集函数，是否有效
     *
     * @return
     */
    @RequestMapping(value = "sum")
    @ResponseBody
    public String sum() {
        String sql = " select sum(IFNULL(case user_name when 'ponket' then age end ,0)) from test_sharding where create_date between '2019-01-01' and '2020-12-30' ";
        Long sum = jdbcTemplate.queryForObject(sql, Long.class);

        return "sum:" + sum;
    }

}
